#!/usr/bin/env python
#-*-encoding:utf-8-*-
'''
Created on 2014年12月18日

@author: chenyongbing
'''
import sys,os,copy
current_dir = os.path.dirname(__file__)
import logging

sys.path.append(os.path.join(current_dir,'../../'))

from base.PyMySQL import ConnectMySQL
from base.PyEmail import *
from base.localConfig import baseLocalConfig
from base.retail_datas import myRetailDatas
from localConfig import LocalConfig
myLocalConfig = LocalConfig()
import sys,datetime
retail_host = baseLocalConfig.mysqlConfig['retail']['host']
retail_user = baseLocalConfig.mysqlConfig['retail']['user']
retail_passwd = baseLocalConfig.mysqlConfig['retail']['passwd']
retail_db = baseLocalConfig.mysqlConfig['retail']['db']

import openpyxl
myRetail = ConnectMySQL(retail_host,retail_user,retail_passwd,retail_db)

class RecordApStatus(object):
    '''
        记录设备状态
    '''
    def __init__(self):
        '''
        Constructor
        '''
        pass
        
    def get_ap_status_with_shop_and_company(self,name='ap_status',start_weekday=4,day=None,stamptime='12:00:00',export_path='/tmp/'):
        company_group = myLocalConfig.rules[name]['comp_group']
        try:
            comp_black_list = myLocalConfig.rules[name]['comp_black_list']
        except:
            comp_black_list = []
        
        query = 'select c.name,g.name,d.deviceid from devices d,group_device gd ,groups g ,companies c where gd.device_id=d.id and g.id=gd.group_id and c.id=d.company_id order by c.name,g.name'    
        if company_group=='' or company_group==None: 
            logging.info('Select Devices From Tab No Group.')
        else:
            query = 'select c.name,g.name,d.deviceid from devices d,group_device gd ,groups g ,companies c where gd.device_id=d.id and g.id=gd.group_id and c.id=d.company_id and d.company_id="%s" order by c.name,g.name'%company_group    
            logging.info('Select Devices From Tab By Group:%s.'%company_group)
        rets = myRetail.SelectAll(query)
        
        if day == None:
            day = datetime.date.today().strftime('%Y-%m-%d %H:%M:%S')
            
        day_date = datetime.date(int(day[:4]),int(day[5:7]),int(day[8:10]))
        weekday = day_date.weekday()
        weekdays = [4,5,6,0,1,2,3]
        show_weekdays =  [weekday]
        start_day = (day_date - datetime.timedelta(weekdays.index(show_weekdays[-1]))).strftime('%Y-%m-%d')
        datas_json_file   = export_path + '/' +'datas.%s.json'%(start_day)
        if os.path.exists(datas_json_file):
            datas = eval(open(datas_json_file).read())
        else:
            datas = {}
        if rets!=None:
            logging.info('Return %s Devices From Tab.'%(len(rets)))
            i = 0
            for ret in rets:
                i+=1
                #if i>10:break
                company,shop_name,device = ret
                #print company,comp_black_list
                if company.encode("utf-8",'ignore') in comp_black_list:
                    logging.warn('Company Name:%s Is In BlackList.'%company)
                    continue
                if not datas.has_key(company):
                    data = []
                elif not datas[company].has_key(device):
                    data = []
                else:
                    data = datas[company][device]['data']
                logging.info('Search Device Status . Device:%s , Shop:%s , Company: %s.'%(device,shop_name,company))
                for weekday_index in show_weekdays:
                    tday = (day_date - datetime.timedelta(show_weekdays.index(show_weekdays[-1]) - show_weekdays.index(weekday_index))).strftime('%Y-%m-%d')
                    status = self.get_stats_from_devices(device=device)
                    #print i,company,shop_name,device,status,day
                    logging.debug('Search Device Status . DeviceNum:%s , Device:%s , Shop:%s , Company:%s ,Status:%s , Day:%s'%(i,device,shop_name,company,status,day))
                    data.append({'status':status,'day':tday})
                if not datas.has_key(company):
                    datas[company] = {}
                if not datas[company].has_key(device):
                    datas[company][device] = {}
                device_datas = datas[company][device]
                device_datas['device'] = device
                device_datas['shop_name'] = shop_name
                device_datas['data'] = data
                #device_datas.append({'device':device,'shop_name':shop_name,'data':data})
                datas[company][device] = device_datas
                
                
                
        else:
            logging.error('Not Return Any Devices From Tab.')
        fr = open(datas_json_file,'w')
        fr.write(str(datas))
        fr.close()
        return datas
    
    def get_ap_status_with_aps(self,name="ap_status_devices",export_file='',day=None,stamptime=None):
        if day==None:
            day = datetime.date.today().strftime("%Y-%m-%d")
        if stamptime==None:
            stamptime = datetime.datetime.today().strftime('%H:%M:%S')
        logging.info('Template %s File :%s'%(name,myLocalConfig.rules[name]['template_file']))
        wb = openpyxl.load_workbook(myLocalConfig.rules[name]['template_file'])
        ws = wb.get_sheet_by_name(wb.get_sheet_names()[0])
        rows = ws.rows 
        
        for i in range(len(rows)):
            if i<1:continue
            row = rows[i]
            device = row[0].value
            logging.info('device:%s'%device)
            if device==None:
                logging.warn('该单元格为空.')
                continue
            device = device.lower()
            status = self.get_stats_from_devices(device=device)
            if status==1:
                status_name = '在线'
            else:
                status_name = '离线'
            logging.debug('Search Device Status . DeviceNum:%s , Device:%s  ,Status:%s , Day:%s'%(i,device,status,day))
            ws.cell(row=i+1,column=2).value = status_name
        wb.save(export_file)
        
    def get_ap_status_with_groups(self,name='ap_status_groups',export_file='',day=None,stamptime=None,groupList=[]):    
        if day==None:
            day = datetime.date.today().strftime("%Y-%m-%d")
        if stamptime==None:
            stamptime = datetime.datetime.today().strftime('%H:%M:%S')
        wb = openpyxl.load_workbook(myLocalConfig.rules[name]['template_file'])
        ws = wb.get_sheet_by_name(wb.get_sheet_names()[0])
        i=1
        for group in groupList:
            #group_name = myAnalyzeConfig.getGroupName(shop_group=group)
            #shopGroups = myAnalyzeConfig.getShopGroups(groupId=group)
            shopGroups = myRetailDatas.get_shop_groups(group)
            #deviceList  = myAnalyzeConfig.getDevices(groups=[group])
            for shop_group  in shopGroups:
                #deviceList = myAnalyzeConfig.getDevices(groups=[shop_group])
                deviceList = myRetailDatas.get_devices_by_groups(groups=[shop_group])
                #group_name = myAnalyzeConfig.getGroupName(shop_group=shop_group)
                group_name  = myRetailDatas.get_group_name(group=shop_group)
                logging.info('Shop Group : %s Group Name : %s Start Select Device Status.'%(shop_group,group_name))
                for device in deviceList:
                    i+=1
                    status = self.get_stats_from_devices(device=device)
                    if status==1:
                        status_name = '在线'
                    else:
                        status_name = '离线'
                    ws.cell(row=i,column=1).value = group_name
                    ws.cell(row=i,column=2).value = device
                    ws.cell(row=i,column=3).value = status_name
        wb.save(export_file)        
          
    def get_weekday_col(self,day):
        day_date = datetime.date(int(day[:4]),int(day[5:7]),int(day[8:10]))
        weekday = day_date.weekday()
        
        if weekday == 4:  start_col = 4
        elif weekday == 5:start_col = 5
        elif weekday == 6:start_col = 6
        elif weekday == 0:start_col = 7
        elif weekday == 1:start_col = 8
        elif weekday == 2:start_col = 9
        elif weekday == 3:start_col = 10
        return start_col
    
    
    def create_sheet(self,wb,company='',company_datas=[],start_row=2):
        #print myLocalConfig.rules[name]['template_file']
        first_ws = wb.get_sheet_by_name(wb.get_sheet_names()[0])
        first_rows = first_ws.rows[0]
        company_rows  = len(company_datas)
        ws = wb.create_sheet(title=company)
#         temp_ws = wb.get_sheet_by_name('template')
#         print ",".join(wb.get_sheet_names())
#         print temp_ws
#         
#         ws = wb.add_sheet(temp_ws)
#         print wb.get_sheet_names(),wb.get_sheet_names()[-1]
#         #ws = wb.get_sheet_by_name(wb.get_sheet_names()[-1])
        #ws.title = company
        for i in range(len(first_rows)):
            first_row = first_rows[i].value
            ws.cell(row=1,column=i+1).value = first_row
        
        ws.merge_cells("A%s:A%s"%(start_row,start_row+company_rows-1))
        ws.cell(row=start_row,column=1).value = company
        for datas in company_datas.values():
            print datas
            shop_name = datas['shop_name']
            device = datas['device']
            ws.cell(row=start_row,column=2).value = device
            ws.cell(row=start_row,column=3).value = shop_name
            for data in datas['data']:
                status = data['status']
                if status in (0,1):
                    status = abs(status-1)
                tday = data['day']
                start_col = self.get_weekday_col(tday)
                ws.cell(row=start_row,column=start_col).value = status
            ws.cell(row=start_row,column=11).value = '=sum(D%s:J%s)'%(start_row,start_row)
            start_row+=1       
            
        
    def export_ap_status_to_xls(self,name='ap_status',datas={},stamptime=None,export_file='',start_row=2):
        if stamptime == None:
            stamptime = datetime.date.today().strftime('%Y-%m-%d %H:%M:%S')
        if os.path.exists(export_file):
            logging.info('Export File %s Already Exists.'%export_file)
        else:
            logging.info('Start Export Data To Xls File %s.'%export_file)
        wb = openpyxl.load_workbook(myLocalConfig.rules[name]['template_file'])
        ws = wb.get_sheet_by_name(wb.get_sheet_names()[0])
      
        
        for company,company_datas in datas.iteritems():
            
            company_rows = len(company_datas)
            
            if company_rows>=50:
                self.create_sheet(wb, company=company, company_datas=company_datas)
                continue
            logging.info('Start Write Company :%s , Total Rows:%s.'%(company,company_rows))
            ws.merge_cells("A%s:A%s"%(start_row,start_row+company_rows-1))
            ws.cell(row=start_row,column=1).value = company
            for datas in company_datas.values():
                shop_name = datas['shop_name']
                device = datas['device']
                ws.cell(row=start_row,column=2).value = device
                ws.cell(row=start_row,column=3).value = shop_name
                vdatas = []
                for data in datas['data']:
                    status = data['status']
                    if status in (0,1):
                        status = abs(status-1)
                    tday = data['day']
                    start_col = self.get_weekday_col(tday)
                    ws.cell(row=start_row,column=start_col).value = status
                    if status!='-':vdatas.append(status)
                #ws.cell(row=start_row,column=11).value = '=sum(D%s:J%s)'%(start_row,start_row)
                if len(vdatas)==0:
                    ws.cell(row=start_row,column=11).value = '-'
                else:
                    ws.cell(row=start_row,column=11).value = sum(vdatas)
                start_row+=1
            #start_row+=1
        logging.info('Save Xls File To %s'%export_file)
        wb.save(export_file)
                
    
    
    
    def get_stats_from_devices(self,tab='devices',device=''):        
        query = 'select status from %s where deviceid ="%s"'%(tab,device)
        ret = myRetail.SelectOne(query)
        if ret:
            status = int(ret[0])
        else:
            status = '-'
        return status
    def get_status(self,tab='devicelog',device='',stamptime=None):  
        if stamptime==None:
            stamptime = datetime.datetime.today().strftime('%Y-%m-%d %H:%M:%S')            
        
        query = 'select status from %s where stamptime<="%s" and deviceid ="%s" order by stamptime desc limit 1'%(tab,stamptime,device)
        ret = myRetail.SelectOne(query)
        if ret:
            status = int(ret[0])
        else:
            status = '-'
        return status
            
        
    def get_ap_status(self,stamptime=None):
        '''改sql python 不支持.'''
        if stamptime==None:
            stamptime = datetime.datetime.today().strftime('%Y-%m-%d %H:%M:%S')
        query = '''
            select T1.deviceid,T1.status,T1.stamptime
            from (
            select deviceid,status,stamptime ,
                @rn :=IF(@prev = deviceid ,@rn+1,1) as rn,
                @prev := deviceid
                from devicelog 
                where stamptime <="2014-12-19 12:00:00" and deviceid in (select deviceid from devices) 
                order  by deviceid,stamptime  desc 
            ) as T1 
            where rn=1
        '''
    
    
    def ap_status_and_send_mail(self,name='ap_status',start_weekday=4,day=None,stamptime='12:00:00',export_file='',groupList=[]):
        mail_user = myLocalConfig.email_user
        mail_pass = myLocalConfig.email_passwd
        mail_host = myLocalConfig.email_smtp
        mail_postfix = myLocalConfig.email_postfix
        sub = '设备状态批量查询'
        content = ''
        #print  myLocalConfig.rules
        to_list = myLocalConfig.rules[name]['mail_to_list']
        if os.path.exists(export_file):
            os.popen('rm -rf "%s"'%export_file)
        if name=='ap_status':
            while 1:
                if os.path.exists(export_file):
                    logging.info('Export File %s Already Exists.'%export_file)
                    break
                export_path = os.path.dirname(export_file)
                datas = self.get_ap_status_with_shop_and_company(name=name,start_weekday=start_weekday, day=day, stamptime=stamptime,export_path=export_path)
                if len(datas)>0:
                    logging.info('Get %s Company Devices Status From Tab.'%len(datas))
                    break
            while 1:
                if not  os.path.exists(export_file):
                    self.export_ap_status_to_xls(name=name,datas=datas, stamptime=stamptime, export_file=export_file)
                if  os.path.exists(export_file):break
        elif name=='ap_status_devices' or re.search('ap_status_devices',name):
            while 1:
                if os.path.exists(export_file):
                    logging.info('Export File %s Already Exists.'%export_file)
                    break
                else:
                    logging.info('Start To Write Excel.')
                self.get_ap_status_with_aps(name=name, export_file=export_file,day=day, stamptime=stamptime)
        elif re.search('ap_status_groups',name):
            while 1:
                if os.path.exists(export_file):
                    logging.info('Export File %s Already Exists.'%export_file)
                    break
                else:
                    logging.info('Start To Write Excel.')
                self.get_ap_status_with_groups(name=name, export_file=export_file, day=day, stamptime=stamptime, groupList=groupList)
        #return 
        while 1:
            atts = [export_file]
            ret = send_mail_with_atts(mail_user,mail_pass,mail_host,mail_postfix,to_list,sub,content,atts=atts)
            if ret==True:
                send_status = True
                logging.info("send mail successed.")
                return
            else:
                wait_time = 10
                logging.info("send mail faild, %s second try again"%wait_time)
                time.sleep(wait_time)
            
            
if __name__ == '__main__':
    import argparse,re,datetime,time
    parser = argparse.ArgumentParser(description='args')
    parser.add_argument('--day',metavar=u"day",default=None)
    parser.add_argument('--stamptime',metavar=u"stamptime",default='12:00:00')
    parser.add_argument('--rule',metavar=u"ap_status\ap_status_devices\ap_status_groups",default='ap_status')
    parser.add_argument('--name',metavar=u"name",default=None)
    parser.add_argument('--groups',metavar=u"groups",default=None)
    args = parser.parse_args()
    day = args.day
    name = args.name
    rule = args.rule
    groups = args.groups
    stamptime = args.stamptime
    myRecordApStatus = RecordApStatus()
    if day==None:
        day = datetime.date.today().strftime('%Y-%m-%d')
    #day = "2014-12-18"
    export_path = myLocalConfig.rules[rule]['export_path']
    #print myLocalConfig.rules[name]['name']
    if name==None and rule=='ap_status':
        export_file = os.path.join(export_path , '设备状态批量查询(%s).xlsx'%day)
    else:
        if name==None:
            name = myLocalConfig.rules[rule]['name']
        export_file = os.path.join(export_path , '设备状态批量查询_%s(%s).xlsx'%(name,day))
    start_weekday = 4
    
    rgroups = myLocalConfig.rules[rule]['comp_group']
    if re.search('ap_status_groups',rule):
        if groups==None and rgroups=='' :
            logging.error('You Must Enter A Group.')
            sys.exit()
        elif rgroups=='':
            groupList = groups.split(',')
        elif groups==None:
            groupList = rgroups.split(',')
        else:
            groupList = groups.split(',')
    else:
        groupList = []
        
        
    
    
    myRecordApStatus.ap_status_and_send_mail(name=rule,start_weekday=start_weekday, day=day, stamptime=stamptime, export_file=export_file,groupList=groupList)
    
    
    
    
    
    
    